﻿using System;
using System.Data;
using System.Collections.Generic;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CARSpocTable
    /// </summary>
    public class CARSpocTable : CacheAbstraction
    {
        public DataTable GetCARSpocList()
        {
            return GetCARSpocList(false);
        }
        public DataTable GetCARSpocList(bool bForceRefresh)
        {
            String sxAdmin = String.Empty;
            if (ICPUser.IsAdmin)
            {
                sxAdmin = "ADMIN";
            }
            
            String sxCacheKey = KCache.V_CAR_SPOC+"_"+sxAdmin;

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                String sxExtraParam = String.Empty;
                String sxQuery = 
                    @"
                    select distinct
                        v1.EMPLOYEE_NO CAR_EMPLOYEE_NO,
                        v1.COMPANY_ID,
                        v1.TOTAL_REV,
                        v2.EMPLOYEE_NAME CAR_NAME,
                        v2.COMPANY_NAME,
                        t2.EMPLOYEE_NO SALES_EMPLOYEE_NO,
                        t3.EMPLOYEE_NAME SALES_NAME,
                        v1.NETWORK_COUNT
                    from
                        (
                            select * from V_ICP_MONSPOC_COMPANY
                            {0}
                        )v1
                        join
                        (
                            select
                                EMPLOYEE_NO,
                                EMPLOYEE_NAME,
                                COMPANY_ID,
                                COMPANY_NAME
                            from
                                V_ICP_CAR                            
                        ) v2
                            on v1.EMPLOYEE_NO = v2.EMPLOYEE_NO
                            and v1.COMPANY_ID = v2.COMPANY_ID
                        left join sni.site t1
                            on v1.COMPANY_ID = t1.COMPANY_ID
                            and t1.P_OFFICE_LEVEL_ID = 1
                        left join sni.site_contact_person t2
                            on t2.site_id = t1.site_id
                            and t2.P_CONTACT_PERSON_TYPE_ID = 2
                            and NVL(t2.VALID_TO, SysDate+1) > SysDate
                        left join slogix.employee t3
                            on t2.EMPLOYEE_NO = t3.EMPLOYEE_NO                             
                    order by
                        v2.EMPLOYEE_NAME asc
                    ";
                DataTable dtTmp;
                if (!ICPUser.IsAdmin)
                {
                    OracleParameter prmID = new OracleParameter();
                    prmID.ParameterName = "prmID";
                    prmID.OracleDbType = OracleDbType.VarChar;
                    prmID.Value = ICPUser.ID;

                    sxExtraParam +=
                                @"where
                                EMPLOYEE_NO IN
                                (
                                    select :prmID from dual
                                    UNION ALL
                                    SELECT DISTINCT NIK
                                    FROM 
                                    v_icp_escalation
                                    START WITH NIK = :prmID        
                                    CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                                )";

                    sxQuery = String.Format(sxQuery, sxExtraParam);

                    dtTmp = dbl.GetDataTable(sxQuery, prmID);
                }
                else
                {
                    sxQuery = String.Format(sxQuery, String.Empty);
                    dtTmp = dbl.GetDataTable(sxQuery);
                }
                
                DataTable dt = dtTmp.Clone();

                String sxLastCarNo = String.Empty;
                String sxLastCompanyId = String.Empty;
                String sxConcat = String.Empty;
                DataRow drNew = null;

                for (int i = 0; i < dtTmp.Rows.Count; i++ )
                {
                    DataRow dr = dtTmp.Rows[i];

                    String sxCurrentCarNo = dr.ReadString("CAR_EMPLOYEE_NO");
                    String sxCurrentCompanyId = dr.ReadString("COMPANY_ID");

                    if (sxLastCarNo != sxCurrentCarNo || sxLastCompanyId != sxCurrentCompanyId)
                    {
                        if (drNew != null)
                        {
                            drNew["SALES_NAME"] = sxConcat;
                            dt.Rows.Add(drNew);
                        }

                        drNew = dt.NewRow();
                        foreach (DataColumn dc in dt.Columns)
                        {
                            drNew[dc.ColumnName] = dr[dc.ColumnName];
                        }

                        sxConcat = dr.ReadString("SALES_NAME");
                    }
                    else
                    {
                        String sxSalesName = dr.ReadString("SALES_NAME");
                        if (!String.IsNullOrEmpty(sxSalesName))
                        {
                            //sxConcat += String.IsNullOrEmpty(sxConcat) ? String.Empty : ", ";
                            //sxConcat += sxSalesName;
                        }
                    }


                    sxLastCarNo = sxCurrentCarNo;
                    sxLastCompanyId = sxCurrentCompanyId;
                }

                if (drNew != null)
                {
                    drNew["SALES_NAME"] = sxConcat;
                    dt.Rows.Add(drNew);
                }

                AddToCache(sxCacheKey, dt, CacheType.Daily);
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        public DataTable GetCARSpocListByCar(String sxFilter)
        {
            return GetCARSpocListByCar(sxFilter, false);
        }
        public DataTable GetCARSpocListByCar(String sxFilter, bool bForceRefresh)
        {
            return new DataView(
                GetCARSpocList(bForceRefresh),
                String.Format("CAR_EMPLOYEE_NO like '{0}%'", sxFilter),
                "CAR_NAME asc",
                DataViewRowState.CurrentRows).ToTable();
        }
		public DataTable GetCARAvailability(DateTime dtmStartDate, int iCustomerId, int iCARId)
        {
            return GetCARAvailability(dtmStartDate, iCustomerId, iCARId, false);
        }
        public DataTable GetCARAvailability(DateTime dtmStartDate, int iCustomerId, int iCARId, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_CAR_AVAILABILITY, dtmStartDate.ToShortDateString(), iCustomerId.ToString(), iCARId.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                String sxExtraParam = String.Empty;
                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartDate";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStartDate;

                if (iCustomerId > 0)
                {
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmCustomerId";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = iCustomerId;

                    sxExtraParam = "where COMPANY_ID = :prmCustomerId";
                }
                else if(iCARId > 0)
                {
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmCARId";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = iCARId;

                    sxExtraParam = "where EMPLOYEE_NO = :prmCARId";
                }
                String sxQuery = String.Format(
                    @"
                    select distinct
                        v2.CAR_ID,
                        v2.CAR_NAME,
                        v2.YEAR,
                        v2.WEEK,
                        v2.MON,
                        v2.TUE,
                        v2.WED,
                        v2.THU,
                        v2.FRI,
                        v1.EMPLOYEE_NO,
                        v1.EMPLOYEE_NAME
                    from
                        (
                        select distinct
                            EMPLOYEE_NO,
                            EMPLOYEE_NAME
                        from
                            V_ICP_CAR
                        {0}
                        ) v1
                    left join
                        (
                        select
                            CAR_ID,
                            CAR_NAME,
                            YEAR,
                            WEEK,
                            MON,
                            TUE,
                            WED,
                            THU,
                            FRI
                        from
                            V_ICP_CAR_AVAILABILITY
                        where
                            WEEK = to_char(:prmStartDate, 'IW')
                        and
                            YEAR = to_char(:prmStartDate, 'YYYY') 
                        ) v2
                    on
                        v1.EMPLOYEE_NO = v2.CAR_ID
                    order by
                        v1.EMPLOYEE_NAME asc",
                     sxExtraParam);

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public override String GetCacheFilename()
        {
            return KCache.Filename.CAR_SPOC;
        }
    }
}